<?php

declare(strict_types=1);

namespace app\curd\controller;

use think\admin\Controller;
use think\admin\helper\QueryHelper;
use think\exception\HttpResponseException;

/**
 * 数据库表备注管理
 * @class Mysql
 * @package app\admin\controller
 */
class Mysql extends Controller
{
    protected $connection = null;
    protected $dbName = null;

    protected $fieldTypes = [
        'tinyint' => [
            'defaultLength' => 3,
            'defaultValue' => '0',
            'isNullable' => false,
        ],
        'int' => [
            'defaultLength' => 10,
            'defaultValue' => '0',
            'isNullable' => false,
        ],
        'bigint' => [
            'defaultLength' => 20,
            'defaultValue' => '0',
            'isNullable' => false,
        ],
        'varchar' => [
            'defaultLength' => 100,
            'defaultValue' => '',
            'isNullable' => true,
        ],
        'date' => [
            'defaultLength' => null,
            'defaultValue' => null,
            'isNullable' => true,
        ],
        'timestamp' => [
            'defaultLength' => null,
            'defaultValue' => null,
            'isNullable' => true,
        ],
        'datetime' => [
            'defaultLength' => null,
            'defaultValue' => null,
            'isNullable' => true,
        ],
        'decimal' => [
            'defaultLength' => '10,2',
            'defaultValue' => '0.00',
            'isNullable' => false,
        ],
        'text' => [
            'defaultLength' => null,
            'defaultValue' => null,
            'isNullable' => true,
        ],
    ];

    // 通用字段列表
    protected $commonFields = [
        [
            'name' => 'person_id',
            'type' => 'int',
            'length' => 11,
            'is_null' => true,
            'comment' => '关联人员ID',
        ],
        [
            'name' => 'data_uuid',
            'type' => 'varchar',
            'length' => 100,
            'is_null' => false,
            'default' => '',
            'comment' => '唯一标识符',
        ],
        [
            'name' => 'name',
            'type' => 'varchar',
            'length' => 100,
            'is_null' => false,
            'default' => '',
            'comment' => '名称',
        ],
        [
            'name' => 'remark',
            'type' => 'varchar',
            'length' => 100,
            'is_null' => true,
            'default' => '',
            'comment' => '备注',
        ],
        [
            'name' => 'file',
            'type' => 'varchar',
            'length' => 100,
            'is_null' => true,
            'default' => '',
            'comment' => '附件路径',
        ],
        [
            'name' => 'is_start_time',
            'type' => 'tinyint',
            'length' => 3,
            'is_null' => false,
            'default' => 0,
            'comment' => '是否记录开始日期',
        ],
        [
            'name' => 'start_time',
            'type' => 'date',
            'is_null' => true,
            'comment' => '开始日期',
        ],
        [
            'name' => 'is_end_time',
            'type' => 'tinyint',
            'length' => 3,
            'is_null' => false,
            'default' => 0,
            'comment' => '是否记录结束日期',
        ],
        [
            'name' => 'end_time',
            'type' => 'date',
            'is_null' => true,
            'comment' => '结束日期',
        ],
        [
            'name' => 'is_monitor',
            'type' => 'tinyint',
            'length' => 3,
            'is_null' => false,
            'default' => 0,
            'comment' => '是否监控',
        ],
        [
            'name' => 'monitor_limit',
            'type' => 'int',
            'length' => 10,
            'is_null' => false,
            'default' => 0,
            'comment' => '监控阈值',
        ],
        [
            'name' => 'json_data',
            'type' => 'text',
            'is_null' => false,
            'comment' => '辅助字段信息',
        ],
        [
            'name' => 'create_at',
            'type' => 'timestamp',
            'is_null' => true,
            'comment' => '创建时间',
        ],
        [
            'name' => 'update_at',
            'type' => 'timestamp',
            'is_null' => true,
            'comment' => '更新时间',
        ],
    ];


    /**
     * 初始化方法
     */
    protected function initialize()
    {
        $this->connection = $this->app->db->connect();
        $this->dbName = $this->connection->getConfig('database');
    }
    /**
     * 数据库表备注管理
     * @auth true
     * @menu true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function index()
    {
        $this->title = '数据库表备注管理';

        // 获取所有表名 用于模糊查询
        $this->table_names = $this->connection->table('information_schema.tables')
            ->field('TABLE_NAME AS table_name, TABLE_COMMENT AS table_comment')
            ->where('TABLE_SCHEMA', $this->dbName)->column('TABLE_NAME AS table_name, TABLE_COMMENT AS table_comment');

        $get = [
            'table_name' => input('table_name', ''),
            'table_comment' => input('table_comment', '')
        ];

        // 构建查询规则，创建 Query 查询对象
        $query = $this->_query("(SELECT * FROM `information_schema`.`tables`) as a", $get);

        $query->layTable(function () use ($get) {
            $this->title = '数据库表字段管理';
        }, function (QueryHelper $query) use ($get) {
            // 查询字段 TABLE_NAME AS table_name, TABLE_COMMENT AS table_comment
            $query->field('a.TABLE_NAME as table_name,a.TABLE_COMMENT as table_comment');

            // 查询条件处理
            $query->like('a.TABLE_NAME#table_name, a.TABLE_COMMENT#table_comment'); //

            $query->where('a.table_schema', $this->dbName)->order('a.table_name asc');
        });
    }

    /**
     * 列表数据处理
     * @param array $data
     */
    protected function _index_page_filter(array &$data)
    {
    }

    /**
     * 数据库表更新
     * @auth true
     */
    public function table_update()
    {
        // 接收参数 table_name 和  table_comment
        $data = $this->_vali([
            'table_name.require' => '未指定表名',
            'key.require' => '修改类型不能为空',
            'value.require' => '修改值不能为空',
        ]);

        if ($data['key'] == 'table_comment') {
            // 修改表注释
            $this->connection->execute("ALTER TABLE `{$data['table_name']}` COMMENT '{$data['value']}'");
        } else {
            if ($data['table_name'] !== $data['value']) {

                // 判断新表名是否存在
                $table = $this->connection->table('information_schema.tables')
                    ->where('TABLE_SCHEMA', $this->dbName) // 指定数据库名
                    ->where('TABLE_NAME', $data['key'])
                    ->find();

                if ($table)
                    $this->error('新表名已存在');
                // 修改表名
                try {
                    $this->connection->execute("RENAME TABLE `{$data['table_name']}` TO `{$data['value']}`");
                    return;
                } catch (HttpResponseException $exception) {
                    throw $exception;
                    return;
                } catch (\Exception $e) {
                    $this->error('Exception 修改失败，请稍后重试' . $e->getMessage());
                    return;
                }
            } else {
                $this->error('新表名不能与原表名相同');
            }
        }
    }

    /**
     * 数据库表字段
     * @auth true
     */
    public function fields()
    {
        $data = $this->_vali([
            'table_name.require' => '未指定表名',
        ]);
        // 构建查询规则，创建 Query 查询对象
        $query = $this->_query("(SELECT * FROM `information_schema`.`columns`) as a");

        // 查询
        $query->layTable(function () use ($data) {
            $this->title = '数据库表字段管理';
            $this->table_name = $data['table_name'];
        }, function (QueryHelper $query) use ($data) {
            $query->where('a.TABLE_SCHEMA', $this->dbName)->where('a.TABLE_NAME', $data['table_name'])->order('a.ORDINAL_POSITION asc');
        });
    }

    /**
     * 列表数据处理
     * @param array $data
     */
    protected function _fields_page_filter(array &$data)
    {
        $this->array_change_key_case_all($data);
    }

    /**
     * 数据库表字段更新
     * @auth true
     */
    public function fields_update()
    {
        // 接收参数
        $data = $this->_vali([
            'table_name.require' => '未指定表名',
            'column_name.require' => '未指定字段',
            'key.require' => '请求异常',
            'value.default' => '',
        ]);

        // 查询字段的已有信息
        $properties = $this->connection->query("SHOW FULL COLUMNS FROM `{$data['table_name']}` WHERE Field = '{$data['column_name']}'");
        if (!$properties) {
            $this->error('字段不存在');
        }

        // 解析字段属性
        $fieldInfo = $this->parseFieldProperties($properties[0]);

        // 处理不同的修改类型
        switch ($data['key']) {
            case 'column_name':
                $fieldInfo['name'] = $data['value'];
                break;
            case 'column_comment':
                $fieldInfo['comment'] = $data['value'];
                break;
            case 'data_type':
                // 切换字段类型时，重置默认值和长度
                if (isset($this->fieldTypes[$data['value']])) {
                    $fieldInfo['type'] = $data['value'];
                    $fieldInfo['length'] = $this->fieldTypes[$data['value']]['defaultLength'];
                    $fieldInfo['default'] = $this->fieldTypes[$data['value']]['defaultValue'];
                    $fieldInfo['is_null'] = $this->fieldTypes[$data['value']]['isNullable'];
                }
                break;
                break;
            case 'column_default':
                $fieldInfo['default'] = $data['value'];
                break;
            case 'numeric_precision':
                $fieldInfo['length'] = $data['value'];
                break;
            default:
                $this->error('不支持的操作类型');
        }

        // 构建 SQL 语句
        $sql = "ALTER TABLE `{$data['table_name']}` " . $this->getFieldSql($data['column_name'], $fieldInfo);

        // $sql = "ALTER TABLE `base_postage_company` MODIFY COLUMN `code_3` varchar(150) NULL COMMENT '官方快递100代码'";

        // 执行 SQL 语句
        try {
            $this->connection->execute($sql);
            $this->success('修改成功' . ' SQL: ' . $sql);
        } catch (HttpResponseException $exception) {
            throw $exception;
        } catch (\Exception $e) {
            $this->error('修改失败' . $e->getMessage() . ' SQL: ' . $sql);
            return;
        }
    }

    /**
     * PHP 多维数组所有key转为小写 || 大写
     * @param $array //数据
     * @param int $case //CASE_LOWER 小写、CASE_UPPER 大写
     * @return array
     */
    protected function array_change_key_case_all(&$data, $case = CASE_LOWER)
    {
        $data = array_change_key_case($data, CASE_LOWER);
        foreach ($data as $key => $vo)
            if (is_array($vo)) {
                $data[$key] = $this->array_change_key_case_all($vo);
            }
        return $data;
    }

    /**
     * 解析字段属性
     * @param array $properties
     * @return array
     */
    protected function parseFieldProperties(array $properties): array
    {
        $typeArr = explode(' ', $properties["Type"]);
        $type = strstr($typeArr[0], "(", true) ?: $typeArr[0];
        $length = preg_match('/\((.*?)\)/', $typeArr[0], $matches) ? $matches[1] : null;

        return [
            'name' => $properties['Field'],
            'type' => $type,
            'length' => $length,
            'default' => $properties['Default'],
            'is_null' => $properties['Null'] === 'YES',
            'unsigned' => in_array('unsigned', $typeArr),
            'comment' => $properties['Comment'],
            'is_primary' => ($properties['Key'] === 'PRI') ? 1 : 0,
            'auto_increment' => ($properties['Extra'] === 'auto_increment') ? 1 : 0,

        ];
    }

    /**
     * 拼接字段属性 用于修改字段生成sql
     * @param string $table
     * @param string $field
     * @return string
     */
    protected function getFieldProperties(string $table, string $field): string
    {
        // 查询字段的已有信息
        $properties = $this->connection->query("SHOW FULL COLUMNS FROM `{$table}` WHERE Field = '{$field}'");
        if (!$properties) {
            $this->error('字段不存在');
        }
        $fieldInfo = $this->parseFieldProperties($properties[0]);

        $sql = "";

        // 确保 type 存在
        if (isset($fieldInfo['type'])) {
            $sql .= " {$fieldInfo['type']}";
        }

        // 处理字段长度
        if (isset($fieldInfo['length']) && $fieldInfo['length'] !== null) {
            $type = strtolower($fieldInfo['type']);
            if (in_array($type, ['tinyint', 'int', 'bigint', 'varchar', 'decimal'])) {
                $sql .= "({$fieldInfo['length']})";
            }
        }

        // 处理是否为空
        if (isset($fieldInfo['is_null']) && $fieldInfo['is_null']) {
            $sql .= " NULL";
        } else {
            $sql .= " NOT NULL";
        }

        // 处理默认值
        if (isset($fieldInfo['default']) && $fieldInfo['default'] !== null) {
            $type = strtolower($fieldInfo['type']);
            if ($type === 'timestamp' && strtoupper($fieldInfo['default']) === 'CURRENT_TIMESTAMP') {
                $sql .= " DEFAULT CURRENT_TIMESTAMP";
            } elseif ($type === 'datetime' && $fieldInfo['default'] === null) {
                $sql .= " DEFAULT NULL";
            } elseif (in_array($type, ['timestamp', 'datetime'])) {
                // 对于 timestamp 和 datetime 字段，默认值必须是合法的日期时间值
                if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $fieldInfo['default'])) {
                    $sql .= " DEFAULT '{$fieldInfo['default']}'";
                } else {
                    // 如果默认值不合法，设置为 NULL
                    $sql .= " DEFAULT NULL";
                }
            } else {
                // 其他字段类型直接设置默认值
                $sql .= " DEFAULT '{$fieldInfo['default']}'";
            }
        }

        // 处理注释
        $comment = isset($fieldInfo['comment']) ? $fieldInfo['comment'] : ucwords($field);
        $sql .= " COMMENT '{$comment}'";

        return $sql;
    }

    /**
     * 生成字段 SQL 语句
     * @param string $field 字段名
     * @param array $fieldInfo 字段信息
     * @param string $fieldType 字段变更类型
     * @return string
     */
    protected function getFieldSql($field, $fieldInfo, $fieldType = '')
    {
        $sql = "";

        // 如果是添加字段
        if ($fieldType === 'add') {
            $sql .= "ADD `{$field}`";
        } else {
            // 如果$fieldInfo['name'] 和 $field 不一样，说明是修改字段名
            if (isset($fieldInfo['name']) && $fieldInfo['name'] !== $field) {
                $sql .= "CHANGE `{$field}` `{$fieldInfo['name']}`";
            } else {
                $sql .= "MODIFY `{$field}`";
            }
        }

        // 确保 type 存在
        if (isset($fieldInfo['type'])) {
            $sql .= " {$fieldInfo['type']}";
        }

        // 处理字段长度
        if (isset($fieldInfo['length']) && $fieldInfo['length'] !== null) {
            $type = strtolower($fieldInfo['type']);
            if (in_array($type, ['tinyint', 'int', 'bigint', 'varchar', 'decimal'])) {
                $sql .= "({$fieldInfo['length']})";
            }
        }

        // 处理是否为空
        if (isset($fieldInfo['is_null']) && $fieldInfo['is_null']) {
            $sql .= " NULL";
        } else {
            $sql .= " NOT NULL";
        }

        // 处理默认值
        if (isset($fieldInfo['default']) && $fieldInfo['default'] !== null) {
            $type = strtolower($fieldInfo['type']);
            if ($type === 'timestamp' && strtoupper($fieldInfo['default']) === 'CURRENT_TIMESTAMP') {
                $sql .= " DEFAULT CURRENT_TIMESTAMP";
            } elseif ($type === 'datetime' && $fieldInfo['default'] === null) {
                $sql .= " DEFAULT NULL";
            } elseif (in_array($type, ['timestamp', 'datetime'])) {
                // 对于 timestamp 和 datetime 字段，默认值必须是合法的日期时间值
                if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $fieldInfo['default'])) {
                    $sql .= " DEFAULT '{$fieldInfo['default']}'";
                } else {
                    // 如果默认值不合法，设置为 NULL
                    $sql .= " DEFAULT NULL";
                }
            } else {
                // 其他字段类型直接设置默认值
                $sql .= " DEFAULT '{$fieldInfo['default']}'";
            }
        }

        /*
        // 只有新增时 处理主键
        if ($fieldType === 'add' && isset($fieldInfo['is_primary']) && $fieldInfo['is_primary']) {
            $sql .= " PRIMARY KEY";
        }

        // 只有新增时 处理自增
        if ($fieldType === 'add' && isset($fieldInfo['auto_increment']) && $fieldInfo['auto_increment'] || $field == 'id') {
            $sql .= " AUTO_INCREMENT";
        }
        */

        // 处理注释
        $comment = isset($fieldInfo['comment']) ? $fieldInfo['comment'] : ucwords($field);
        $sql .= " COMMENT '{$comment}'";

        return $sql;
    }

    /**
     * 批量添加字段
     * @auth true
     */
    public function fields_batch_add()
    {
        if ($this->request->isGet()) {
            $data = $this->_vali([
                'table_name.require' => '未指定表名',
            ]);

            $this->fetch('fields_batch_add', [
                'table_name' => $data['table_name'],
                'commonFields' => $this->commonFields,
            ]);
        } else {
            // 接收参数
            $data = $this->_vali([
                'table_name.require' => '未指定表名',
                'fields.require' => '未选择字段',
            ]);

            // 查询表已有字段
            $hasFields = $this->connection->query("SHOW FULL COLUMNS FROM `{$data['table_name']}`");
            if (!$hasFields) {
                $this->error('表不存在');
            }

            // 将字段列表转换为以字段名为键的关联数组
            $hasFields = array_column($hasFields, null, 'Field');

            // 遍历用户选择的字段，生成 SQL 并执行
            $commonFields = array_column($this->commonFields, null, 'name');
            foreach ($data['fields'] as $fieldName) {
                if (isset($commonFields[$fieldName])) {
                    $field = $commonFields[$fieldName];
                    $fieldType = (!isset($hasFields[$fieldName])) ? 'add' : '';
                    $sql = "ALTER TABLE `{$data['table_name']}` {$this->getFieldSql($fieldName, $field, $fieldType)}";
                    try {
                        $this->connection->execute($sql);
                    } catch (HttpResponseException $exception) {
                        throw $exception;
                    } catch (\Exception $e) {
                        $this->error('字段添加失败：' . $e->getMessage() . ' SQL: ' . $sql);
                        return;
                    }
                }
            }
            $this->success('字段添加成功', "javascript:$('#TablesFieldsTable').trigger('reload');");
        }
    }

    /**
     * 添加字段
     * @auth true
     */
    public function fields_add()
    {
        if ($this->request->isGet()) {
            $data = $this->_vali([
                'table_name.require' => '未指定表名',
            ]);
        } else {
            // 接收参数
            $data = $this->_vali([
                'add.default' => '1',
                'table_name.require' => '未指定表名',
                'name.require' => '未指定字段名',
                'type.require' => '未指定字段类型',
                'comment.require' => '未指定字段注释',
            ]);
        }
        $this->_fields_form_filter($data);
    }

    /**
     * 编辑字段
     * @auth true
     */
    public function fields_edit()
    {
        if ($this->request->isGet()) {
            $data = $this->_vali([
                'table_name.require' => '未指定表名',
                'name.require' => '未指定字段名',
            ]);
        } else {
            // 接收参数
            $data = $this->_vali([
                'table_name.require' => '未指定表名',
                'name.require' => '未填写字段名',
                'comment.default' => '',
                'length.default' => '',
                'default.default' => '',
            ]);
        }
        $this->_fields_form_filter($data);
    }

    /**
     * 删除字段
     * @auth true
     */
    public function fields_remove()
    {
        if ($this->request->isPost()) {
            $data = $this->_vali([
                'table_name.require' => '未指定表名',
                'column_name.require' => '未指定字段名',
            ]);
            // 构建 SQL 语句
            $sql = "ALTER TABLE `{$data['table_name']}` DROP COLUMN `{$data['column_name']}`";

            // 执行 SQL 语句
            try {
                $this->connection->execute($sql);
                $this->success('修改成功' . ' SQL: ' . $sql);
            } catch (HttpResponseException $exception) {
                throw $exception;
            } catch (\Exception $e) {
                $this->error('修改失败' . $e->getMessage() . ' SQL: ' . $sql);
                return;
            }
        }
    }

    /**
     * 表单数据处理
     * @param array $data
     */
    protected function _fields_form_filter(array &$vo)
    {
        if ($this->request->isGet()) {
            //$this->fieldTypes = $this->fieldTypes;
            // 查询指定字段信息
            if (isset($vo['name']) && !empty($vo['name'])) {
                // 查询字段的已有信息
                $properties = $this->connection->query("SHOW FULL COLUMNS FROM `{$vo['table_name']}` WHERE Field = '{$vo['name']}'");
                if (!$properties) {
                    $this->error('字段不存在');
                }

                // 解析字段属性
                $vo = array_merge($this->parseFieldProperties($properties[0]), $vo);
            }
            $this->fetch('fields_form', ['vo' => $vo]);
        } else {
            $fieldType = ''; // 添加字段设置为add 修改字段设置为空即可

            // 修改时先查询字段信息，然后更新长度和默认值
            if (isset($vo['add']) && $vo['add'] == 1) {
                $vo['length'] = $this->fieldTypes[$vo['type']]['defaultLength'];
                $vo['default'] = $this->fieldTypes[$vo['type']]['defaultValue'];
                $vo['is_null'] = $this->fieldTypes[$vo['type']]['isNullable'];
                $fieldType = 'add';
            } else {
                // 查询字段的已有信息
                $properties = $this->connection->query("SHOW FULL COLUMNS FROM `{$vo['table_name']}` WHERE Field = '{$vo['name']}'");
                if (!$properties) {
                    $this->error('字段不存在');
                }
                // 解析字段属性
                $vo = array_merge($this->parseFieldProperties($properties[0]), $vo);
            }

            // 构建 SQL 语句
            $sql = "ALTER TABLE `{$vo['table_name']}` " . $this->getFieldSql($vo['name'], $vo, $fieldType);

            // $sql = "ALTER TABLE `base_postage_company` MODIFY COLUMN `code_3` varchar(150) NULL COMMENT '官方快递100代码'";

            // 执行 SQL 语句
            try {
                $this->connection->execute($sql);
                $this->success('修改成功' . ' SQL: ' . $sql, "javascript:$('#TablesFieldsTable').trigger('reload');");
            } catch (HttpResponseException $exception) {
                throw $exception;
            } catch (\Exception $e) {
                $this->error('修改失败' . $e->getMessage() . ' SQL: ' . $sql);
                return;
            }
        }
    }

    /**
     * 数据库表字段排序
     * @auth true
     */
    public function fields_sort()
    {
        $data = $this->_vali([
            'table_name.require' => '未指定表名',
        ]);

        // 获取字段列表
        $fields = $this->connection->table('information_schema.columns')
            ->where('TABLE_SCHEMA', $this->dbName)
            ->where('TABLE_NAME', $data['table_name'])
            ->order('ORDINAL_POSITION', 'asc')
            ->select()
            ->toArray();
        $fields = $this->array_change_key_case_all($fields);

        if ($this->request->isGet()) {
            // 渲染视图，传递字段列表
            $this->fetch('fields_sort', ['table_name' => $data['table_name'], 'fields' => $fields]);
        } else {
            // 接收排序数据
            $data = $this->_vali([
                'table_name.require' => '未指定表名',
                'ordinal_position.require' => '字段异常',
            ]);

            // 获取新的排序数组
            $new_sort_array = $data['ordinal_position'];
            if (!is_array($new_sort_array)) {
                $this->error('排序数据格式错误，必须为数组');
            }

            // 将字段列表按 ordinal_position 映射为键值对
            $fieldMap = [];
            foreach ($fields as $field) {
                $fieldMap[$field['ordinal_position']] = $field['column_name'];
            }

            // 遍历新的排序数组，生成并执行 SQL 语句
            foreach ($new_sort_array as $index => $ordinalPosition) {


                // 第一个字段默认不参与排序
                if ($index > 0) {
                    // 获取字段名称
                    $fieldName = $fieldMap[$ordinalPosition];
                    if (empty($fieldName)) {
                        $this->error("未找到 ordinal_position 为 {$ordinalPosition} 的字段");
                    }

                    // 将字段移动到上一个字段的后面
                    $previousField = $fieldMap[$new_sort_array[$index - 1]];
                    $sql = "ALTER TABLE `{$data['table_name']}` MODIFY COLUMN `{$fieldName}` {$this->getFieldProperties($data['table_name'], $fieldName)} AFTER `{$previousField}`";

                    // 执行 SQL 语句
                    try {
                        $this->connection->execute($sql);
                    } catch (\Exception $e) {
                        $this->error('字段排序失败：' . $e->getMessage() . ' SQL: ' . $sql);
                        return;
                    }
                }
            }
            $this->success('字段排序成功', "javascript:$('#TablesFieldsTable').trigger('reload');");
        }

    }
}
